﻿namespace WMS6.SQLServerDAL
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    using WMS6.Common;
    using WMS6.DataEntity;
    using WMS6.DBUtility;
    using WMS6.IDAL;
    using WMS6.QueryEntity;

    public class DbConfigDA : IDbConfigDA
    {
        private string CREATED_BY = "@CREATED_BY";
        private string DB_DATABASE = "@DB_DATABASE";
        private string DB_DBMS = "@DB_DBMS";
        private string DB_LOGIN_ID = "@DB_LOGIN_ID";
        private string DB_LOGIN_PASSWD = "@DB_LOGIN_PASSWD";
        private string IS_ACTIVE = "@IS_ACTIVE";
        private string IS_GLOBAL = "@IS_GLOBAL";
        private string SQL_CHECK_DB_CONFIG_ID_UNIQUE = " SELECT COUNT(1) FROM SYSADM.DB_CONFIG WHERE WH_DB_ID = @WH_DB_ID ";
        private string SQL_DELETE_DB_CONFIG = " DELETE FROM SYSADM.DB_CONFIG WHERE WH_DB_ID = @WH_DB_ID ";
        private string SQL_INSERT_DB_CONFIG = " INSERT INTO SYSADM.DB_CONFIG (  WH_DB_NAME, WH_DB_DESC, DB_DBMS, DB_DATABASE, DB_LOGIN_ID, DB_LOGIN_PASSWD, IS_GLOBAL, IS_ACTIVE, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE) VALUES (  @WH_DB_NAME, @WH_DB_DESC, @DB_DBMS, @DB_DATABASE, @DB_LOGIN_ID, @DB_LOGIN_PASSWD, @IS_GLOBAL, @IS_ACTIVE, @CREATED_BY, GETDATE(), @UPDATED_BY, GETDATE() )  ";
        private string SQL_SELECT_ALL_DB_CONFIG = " SELECT  WH_DB_ID, WH_DB_NAME, WH_DB_DESC, DB_DBMS, DB_DATABASE, DB_LOGIN_ID, DB_LOGIN_PASSWD, IS_GLOBAL, IS_ACTIVE, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE  FROM  SYSADM.DB_CONFIG ";
        private string SQL_UPDATE_DB_CONFIG = " UPDATE SYSADM.DB_CONFIG SET WH_DB_NAME = @WH_DB_NAME, WH_DB_DESC = @WH_DB_DESC, DB_DBMS = @DB_DBMS, DB_DATABASE = @DB_DATABASE, DB_LOGIN_ID = @DB_LOGIN_ID, DB_LOGIN_PASSWD = @DB_LOGIN_PASSWD, IS_GLOBAL = @IS_GLOBAL, IS_ACTIVE = @IS_ACTIVE, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE WH_DB_ID = @WH_DB_ID  ";
        private string UPDATED_BY = "@UPDATED_BY";
        private string WH_DB_DESC = "@WH_DB_DESC";
        private string WH_DB_ID = "@WH_DB_ID";
        private string WH_DB_NAME = "@WH_DB_NAME";

        public bool CheckDbConfigIDUnique(string dbConfigID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@WH_DB_ID", dbConfigID) };
            return (DBHelper.ExecuteScalar(CommandType.Text, this.SQL_CHECK_DB_CONFIG_ID_UNIQUE, paras).ToString() == "1");
        }

        public bool CheckDbConfigIDUnique(DataBase dataBase, DbTransaction tran, string dbConfigID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@WH_DB_ID", dbConfigID) };
            return (dataBase.ExecuteScalar(tran, CommandType.Text, this.SQL_CHECK_DB_CONFIG_ID_UNIQUE, paras).ToString() == "1");
        }

        public int DeleteDbConfig(List<string> dbConfigID)
        {
            int result = 0;
            DataBase dataBase = new DataBase();
            DbConnection conn = dataBase.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int j = 0; j < dbConfigID.Count; j++)
                {
                    SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@WH_DB_ID", dbConfigID[j]) };
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_DB_CONFIG, paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int DeleteDbConfig(DataBase dataBase, DbTransaction tran, List<string> dbConfigID)
        {
            int result = 0;
            try
            {
                for (int j = 0; j < dbConfigID.Count; j++)
                {
                    SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@WH_DB_ID", dbConfigID[j]) };
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_DB_CONFIG, paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public DataSet GetAllDbConfig()
        {
            string sql = this.SQL_SELECT_ALL_DB_CONFIG;
            return DBHelper.ExecuteDataSet(CommandType.Text, sql);
        }

        public DataSet GetAllDbConfig(DataBase dataBase, DbTransaction tran)
        {
            string sql = this.SQL_SELECT_ALL_DB_CONFIG;
            return dataBase.ExecuteDataSet(tran, CommandType.Text, sql);
        }

        public DbConfigInfo GetDbConfigByDBLogindID(string dbLogindID)
        {
            string sql = this.SQL_SELECT_ALL_DB_CONFIG + " WHERE DB_LOGIN_ID = @DB_LOGIN_ID  AND IS_GLOBAL='N'  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@DB_LOGIN_ID", dbLogindID) };
            DbConfigInfo dbConfigInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                if (reader.Read())
                {
                    dbConfigInfo = new DbConfigInfo((reader["WH_DB_ID"].ToString() != "") ? int.Parse(reader["WH_DB_ID"].ToString()) : 0, reader["WH_DB_NAME"].ToString(), reader["WH_DB_DESC"].ToString(), reader["DB_DBMS"].ToString(), reader["DB_DATABASE"].ToString(), reader["DB_LOGIN_ID"].ToString(), reader["DB_LOGIN_PASSWD"].ToString(), reader["IS_GLOBAL"].ToString(), reader["IS_ACTIVE"].ToString(), reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : new DateTime(), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime());
                }
            }
            return dbConfigInfo;
        }

        public DbConfigInfo GetDbConfigByID(string dbconfigID)
        {
            string sql = this.SQL_SELECT_ALL_DB_CONFIG + " WHERE WH_DB_ID = @WH_DB_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@WH_DB_ID", dbconfigID) };
            DbConfigInfo dbConfigInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                if (reader.Read())
                {
                    dbConfigInfo = new DbConfigInfo((reader["WH_DB_ID"].ToString() != "") ? int.Parse(reader["WH_DB_ID"].ToString()) : 0, reader["WH_DB_NAME"].ToString(), reader["WH_DB_DESC"].ToString(), reader["DB_DBMS"].ToString(), reader["DB_DATABASE"].ToString(), reader["DB_LOGIN_ID"].ToString(), reader["DB_LOGIN_PASSWD"].ToString(), reader["IS_GLOBAL"].ToString(), reader["IS_ACTIVE"].ToString(), reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : new DateTime(), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime());
                }
            }
            return dbConfigInfo;
        }

        public DbConfigInfo GetDbConfigByID(DataBase dataBase, DbTransaction tran, string dbconfigID)
        {
            string sql = this.SQL_SELECT_ALL_DB_CONFIG + " WHERE WH_DB_ID = @WH_DB_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@WH_DB_ID", dbconfigID) };
            DbConfigInfo dbConfigInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            if (reader.Read())
            {
                dbConfigInfo = new DbConfigInfo((reader["WH_DB_ID"].ToString() != "") ? int.Parse(reader["WH_DB_ID"].ToString()) : 0, reader["WH_DB_NAME"].ToString(), reader["WH_DB_DESC"].ToString(), reader["DB_DBMS"].ToString(), reader["DB_DATABASE"].ToString(), reader["DB_LOGIN_ID"].ToString(), reader["DB_LOGIN_PASSWD"].ToString(), reader["IS_GLOBAL"].ToString(), reader["IS_ACTIVE"].ToString(), reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : new DateTime(), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime());
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return dbConfigInfo;
        }

        public DataSet GetDbConfigByQueryList(List<string> sqlWhere, DbConfigQueryEntity dbconfigQuery)
        {
            string temp = this.SQL_SELECT_ALL_DB_CONFIG;
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!dbconfigQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, dbconfigQuery.CurrentPage, dbconfigQuery.PageSize, dbconfigQuery.SortField, dbconfigQuery.SortDirection);
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, temp);
        }

        public DataSet GetDbConfigByQueryList(DataBase dataBase, DbTransaction tran, List<string> sqlWhere, DbConfigQueryEntity dbconfigQuery)
        {
            string temp = this.SQL_SELECT_ALL_DB_CONFIG;
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!dbconfigQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, dbconfigQuery.CurrentPage, dbconfigQuery.PageSize, dbconfigQuery.SortField, dbconfigQuery.SortDirection);
            }
            return dataBase.ExecuteDataSet(tran, CommandType.Text, temp);
        }

        public int InsertDbConfig(DbConfigInfo dbConfigInfo)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                SqlParameter[] paras = this.Set_DbConfig_Parameters(dbConfigInfo);
                if (paras != null)
                {
                    result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_DB_CONFIG, paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int InsertDbConfig(DataBase dataBase, DbTransaction tran, DbConfigInfo dbConfigInfo)
        {
            int result = 0;
            try
            {
                SqlParameter[] paras = this.Set_DbConfig_Parameters(dbConfigInfo);
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_DB_CONFIG, paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        private SqlParameter[] Set_DbConfig_Parameters(DbConfigInfo dbConfigInfo)
        {
            SqlParameter[] paramArray = new SqlParameter[] { new SqlParameter(this.WH_DB_ID, SqlDbType.Int, 4), new SqlParameter(this.WH_DB_NAME, SqlDbType.VarChar, 30), new SqlParameter(this.WH_DB_DESC, SqlDbType.VarChar, 50), new SqlParameter(this.DB_DBMS, SqlDbType.VarChar, 30), new SqlParameter(this.DB_DATABASE, SqlDbType.VarChar, 120), new SqlParameter(this.DB_LOGIN_ID, SqlDbType.VarChar, 20), new SqlParameter(this.DB_LOGIN_PASSWD, SqlDbType.VarChar, 50), new SqlParameter(this.IS_GLOBAL, SqlDbType.VarChar, 1), new SqlParameter(this.IS_ACTIVE, SqlDbType.VarChar, 1), new SqlParameter(this.CREATED_BY, SqlDbType.VarChar, 20), new SqlParameter(this.UPDATED_BY, SqlDbType.VarChar, 20) };
            paramArray[0].Value = dbConfigInfo.WhDbID;
            paramArray[1].Value = dbConfigInfo.WhDbName;
            paramArray[2].Value = dbConfigInfo.WhDbDesc;
            paramArray[3].Value = dbConfigInfo.DbDbms;
            if (!string.IsNullOrEmpty(dbConfigInfo.DbDatabase))
            {
                paramArray[4].Value = dbConfigInfo.DbDatabase;
            }
            else
            {
                paramArray[4].Value = DBNull.Value;
            }
            paramArray[5].Value = dbConfigInfo.DbLoginID;
            paramArray[6].Value = dbConfigInfo.DbLoginPasswd;
            paramArray[7].Value = dbConfigInfo.IsGlobal;
            if (!string.IsNullOrEmpty(dbConfigInfo.IsActive))
            {
                paramArray[8].Value = dbConfigInfo.IsActive;
            }
            else
            {
                paramArray[8].Value = DBNull.Value;
            }
            paramArray[9].Value = dbConfigInfo.CreatedBy;
            paramArray[10].Value = dbConfigInfo.UpdatedBy;
            return paramArray;
        }

        public int UpdateDbConfig(DbConfigInfo dbConfigInfo)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                SqlParameter[] paras = this.Set_DbConfig_Parameters(dbConfigInfo);
                if (paras != null)
                {
                    result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_DB_CONFIG, paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int UpdateDbConfig(DataBase dataBase, DbTransaction tran, DbConfigInfo dbConfigInfo)
        {
            int result = 0;
            try
            {
                SqlParameter[] paras = this.Set_DbConfig_Parameters(dbConfigInfo);
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_DB_CONFIG, paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }
    }
}

